# -----------------------------
# 配置
# -----------------------------
$instances = Import-Csv ".\instances.csv"   # Server,User,Password
$sqlFolder = ".\SqlScripts"
$logFile = ".\SyncLog.txt"

"" | Out-File -FilePath $logFile -Encoding UTF8

# 收集 SQL 文件
$sqlFiles = Get-ChildItem $sqlFolder -Filter *.sql

# -----------------------------
# 统计总任务数
# -----------------------------
$totalTasks = 0
$completedTasks = 0

foreach ($row in $instances) {
    $server = $row.Server
    $user   = $row.User
    $pass   = $row.Password

    try {
        $connStr = if ([string]::IsNullOrEmpty($user)) {
            "Server=$server;Database=master;Integrated Security=True;"
        } else {
            "Server=$server;Database=master;User ID=$user;Password=$pass;"
        }

        $conn = New-Object System.Data.SqlClient.SqlConnection $connStr
        $conn.Open()

        $cmd = $conn.CreateCommand()
        $cmd.CommandText = "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;"
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapter.Fill($ds) | Out-Null

        $totalTasks += $ds.Tables[0].Rows.Count * $sqlFiles.Count
        $conn.Close()
    } catch {
        Write-Host "无法获取数据库列表: $server | $_" -ForegroundColor Red
    }
}

Write-Host "总任务数: $totalTasks" -ForegroundColor Cyan

# -----------------------------
# 同步执行
# -----------------------------
$startTime = Get-Date
"=== 同步开始: $startTime ===" | Out-File -FilePath $logFile -Append
Write-Host "=== 同步开始: $startTime ===" -ForegroundColor Cyan

foreach ($row in $instances) {
    $server = $row.Server
    $user   = $row.User
    $pass   = $row.Password
    $connStr = if ([string]::IsNullOrEmpty($user)) {
        "Server=$server;Database=master;Integrated Security=True;"
    } else {
        "Server=$server;Database=master;User ID=$user;Password=$pass;"
    }

    Write-Host "`n正在连接实例: $server" -ForegroundColor Cyan

    try {
        $conn = New-Object System.Data.SqlClient.SqlConnection $connStr
        $conn.Open()

        # 获取用户数据库
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0;"
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $cmd
        $ds = New-Object System.Data.DataSet
        $adapter.Fill($ds) | Out-Null

        $conn.Close()

        foreach ($dbRow in $ds.Tables[0].Rows) {
            $dbName = $dbRow["name"]
            Write-Host "  -> 检查数据库: $dbName" -ForegroundColor Green

            # 遍历 SQL 文件
            foreach ($sqlFile in $sqlFiles) {
                $sqlName = $sqlFile.Name
                $sqlText = Get-Content $sqlFile.FullName -Raw -Encoding UTF8
                # -----------------------------
                # 解析对象类型和名称
                # -----------------------------
                if ($sqlText -match '(?i)CREATE\s+(PROCEDURE|PROC|VIEW|FUNCTION|TRIGGER|TABLE)\s+([\[\]a-zA-Z0-9_\.]+)') {
                    $objType = $matches[1].ToUpper()
                    $objName = $matches[2]

                    Write-Host "     脚本: $sqlName | 对象: $objType $objName" -ForegroundColor Yellow

                    try {
                        $connDbStr = if ([string]::IsNullOrEmpty($user)) {
                            "Server=$server;Database=$dbName;Integrated Security=True;"
                        } else {
                            "Server=$server;Database=$dbName;User ID=$user;Password=$pass;"
                        }

                        $connDb = New-Object System.Data.SqlClient.SqlConnection $connDbStr
                        $connDb.Open()
                        $cmdCheck = $connDb.CreateCommand()

                        switch ($objType) {
                            "PROCEDURE" { $cmdCheck.CommandText = "SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'$objName') AND type = 'P'" }
                            "PROC"      { $cmdCheck.CommandText = "SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'$objName') AND type = 'P'" }
                            "VIEW"      { $cmdCheck.CommandText = "SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'$objName') AND type = 'V'" }
                            "FUNCTION"  { $cmdCheck.CommandText = "SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'$objName') AND type IN ('FN','IF','TF')" }
                            "TRIGGER"   { $cmdCheck.CommandText = "SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N'$objName')" }
                            "TABLE"     { $cmdCheck.CommandText = "SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID(N'$objName')" }
                        }

                        $exists = $cmdCheck.ExecuteScalar()
                        if ($exists) {
                            $msg = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') | $server | $dbName | $sqlName | 已存在 | 跳过"
                            $msg | Out-File -FilePath $logFile -Append
                            Write-Host "       -> 已存在，跳过" -ForegroundColor DarkYellow
                        } else {
                            # 执行 SQL
                            $cmdExec = $connDb.CreateCommand()
                            $cmdExec.CommandText = $sqlText
                            $cmdExec.ExecuteNonQuery()

                            $msg = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') | $server | $dbName | $sqlName | 成功"
                            $msg | Out-File -FilePath $logFile -Append
                            Write-Host "       -> 已创建" -ForegroundColor Green
                        }

                        $connDb.Close()

                    } catch {
                        $msg = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') | $server | $dbName | $sqlName | 失败 | $($_.Exception.Message)"
                        $msg | Out-File -FilePath $logFile -Append
                        Write-Host "       !! 执行失败: $($_.Exception.Message)" -ForegroundColor Red
                    }

                } else {
                    Write-Host "     !! 无法识别对象类型: $sqlName" -ForegroundColor Red
                    $msg = "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') | $server | $dbName | $sqlName | 无法识别对象类型"
                    $msg | Out-File -FilePath $logFile -Append
                }

                # -----------------------------
                # 更新进度百分比
                # -----------------------------
                $completedTasks++
                $percent = [math]::Round(($completedTasks / $totalTasks) * 100, 1)
                Write-Host ("       进度: {0}/{1} ({2}%)" -f $completedTasks, $totalTasks, $percent) -ForegroundColor Cyan
            }
        }

    } catch {
        Write-Host "  !! 连接实例 $server 失败: $_" -ForegroundColor Red
        $completedTasks += $sqlFiles.Count # 估算跳过
    }
}

$endTime = Get-Date
"=== 同步结束: $endTime ===" | Out-File -FilePath $logFile -Append
Write-Host "=== 同步结束: $endTime ===" -ForegroundColor Cyan
Write-Host "执行完成，日志保存在 $logFile" -ForegroundColor Yellow
